В Excel таблицах, особенно больших, дубликаты данных – довольно частое явление. Иногда они нужны и их можно не трогать, иногда их следует удалить, чтобы они не влияли на статистику, а иногда их просто нужно выделить.

Чтобы быстро найти повторяющиеся значения:

1. Выделите диапазон, в котором нужно найти повторяющиеся значения.

2. На вкладке «Главная» выберите «Условное форматирование» → «Правило выделения ячеек» → «Повторяющиеся значения» → «ОК».

3. В разделе «Сортировка и фильтр» выберите «Фильтр» и установите фильтр по цвету выделения.

После всех манипуляций в списке останутся только одинаковые выделенные значения. Если вам затем нужно увидеть уникальные ячейки из этого списка, используйте функцию UNIQUE.

Еще больше интересных и полезных советов вы найдете в моем телеграмм канале : https://t.me/TechLiveHack

Друзья, всем привет. Сегодня хочу рассказать вам про несколько полезных и интересных (с моей скромной и субъективной точки зрения) трюков при работе со сводными таблицами. Здесь не будет подробного разбора про работу в сводных таблицах: что это, для чего и почему. Материал предназначен для тех, кто уже хоть как-то знаком с этим прекрасным инструментом и знает, что это за зверь такой. Если же вы ещё не работали со сводными таблицами, при этом проводите много времени в Excel и строите разного рода отчёты, аналитику, то я вам настоятельно рекомендую как можно скорее освоить этот поистине чудесный инструмент. Поверьте, вы откроете для себя абсолютно новый мир :)

1 - Фильтры для полей сводной, которые по умолчанию фильтровать нельзя.

Поля, которые находятся в области значений, нельзя фильтровать привычным для нас образом. Там просто нет фильтров, и установить их обычным способом нельзя, команда не активна:

Но если нельзя, но очень хочется, то можно.

Решение: нужно поставить курсор в соседнюю ячейку справа от заголовка поля и установить фильтр. Всё, готово. Теперь можно фильтровать значения, как в обычной таблице.

2 - Изменение порядка элементов в списке.

Вообще, есть несколько способов менять порядок элементов в сводной. Я хочу рассказать вам про наиболее удобный. Предположим, мы хотим, чтобы список менеджеров начинался с Чайниковой:

Решение: выбираем ячейку с Баранкиным и вероломно начинаем печатать фамилию Чайниковой. Целиком печатать не нужно, программа сама предложит готовый вариант. Нажимаем Enter. Вуаля! Теперь Чайникова на вершине нашего списка:

Ещё один способ: это обычное копирование и вставка. Выбираем ячейку с Чайниковой, копируем её. Далее выбираем ячейку с Баранкиным (место, куда хотим переместить наше значение), и вставляем.

3 - Использование собственных списков для сортировки.

Прошлый способ хорош, если нужно сделать это однократно. Но если у нас уже есть какой-то устоявшийся порядок, он вот совсем не по алфавиту, а бездушный Excel всегда всё сортирует именно по алфавиту?

Решение: многие знают, что если мы напишем в ячейке "январь" ("февраль", "март" и т.д.), потом протащим ячейку за правый нижний угол вниз, то всемогущий Excel автоматически заполнит следующие ячейки месяцами по порядку. Но не только лишь все знают, что такую штуку можно сделать и по своему списку.
Шаг 1. Создаём свой список (с блэкджеком и порядком). Где-нибудь на листе Excel создаём список в том порядке, в котором нам нужно. Далее заходим в Файл - Параметры - Дополнительно - Изменить списки:

В поле "Импорт списка из ячеек:" указываем диапазон, в котором находится наш список, далее нажимаем Импорт. Всё, подготовительная работа завершена:

Шаг 2. Для сортировки данных используем наш список. Теперь можно применить сортировку от А до Я в столбце с менеджерами. И вот ведь неожиданность, сортировка будет не по алфавиту, а согласно нашему списку:

К сожалению, данный приём имеет ограничения в части обновления списка. Если добавится новый сотрудник, то при обновлении сводной сотрудник будет в самом конце списка независимо от того, какая у него там первая буква фамилии. Дальше либо добавлять его в список, либо вручную перетаскивать в нужное место.
Если нужно будет вернуть стандартную православную сортировку по алфавиту для данного списка, необходимо раскрыть значок фильтра, выбрать Дополнительные параметры сортировки - по возрастанию - Дополнительно - снять галочку с "Автоматическая сортировка..." - в поле ниже выбрать "Без вычислений":

Кстати, созданные нами списки будут работать так же, как встроенные. То есть если вписать в ячейку Баранкина, протащить вниз, в следующих ячейках будут фамилии по списку.

4 - Отображение пустых строк.

При разбивке какого-то показателя на несколько групп/категорий в сводных таблицах не отображаются данные, которых нет в источнике. В целом, это логично. Но согласитесь, что если мы, к примеру, хотим проанализировать показатель по сотрудникам по месяцам, то вот такая картина будет только нас запутывать:

Месяцы идут по порядку, да, но у кого-то пропущен июль, у кого-то февраль и октябрь.

Решение - щёлкаем правой кнопкой мыши по любой ячейке того поля, в котором хотим отображать ВСЕ элементы (в том числе и пустые) - далее Параметры поля... - вкладка Разметка и печать - ставим галочку Отображать пустые элементы.

У этого приёма есть один побочный эффект - если речь идёт про работу с датами, то отображаются даты до самого раннего числа и после него (видно на гифке). Избавиться от этого довольно просто: нужно убрать их через фильтр, сняв галочки.

5 - Топ-N сотрудников/месяцев/товаров и так далее.

Вообще, отображение топ-3/5/10 сотрудников или любого другого - это не привилегия фильтра в сводной таблице. Работая с фильтрами в обычных таблицах, у нас есть точно такая же возможность. Но про эту возможность, исходя из моего хоть и небогатого, но всё же опыта, мало кто знает. То ли нафиг никому не нужно, то ли просто пользователи не знают. Как по мне, вещь полезная. Особенно с учётом того, что в сводной таблице фильтр будет обновляться вместе с внесением изменений в источник (и обновления самой сводной, конечно же), чего в обычных таблицах, увы, нет.

Решение - всё просто. Раскрываем фильтр по тому полю сводной, по которому хотим выделить топ-N чего-нибудь - далее Фильтр по значению - Первые 10 - указываем нужные нам настройки.

Правда, без побочных эффектов тут, к сожалению, тоже не обошлось. Если у какого-то сотрудника не будет хватать данных для того, чтобы отобразить топ-3, то по этому сотруднику (или по тому, по чему вы ищете топ-N) будут вообще все данные. Поведение более чем странное, но как есть :(

6 - Повтор подписей и промежуточные итоги для отдельных полей, а не для всей таблицы.

Когда у нас с вами в сводной таблице в области строк больше двух полей, то сразу возникает вопрос про промежуточные итоги. И если идти стандартным путём, то есть через вкладку Конструктор - группа Макет - Промежуточные итоги, то там мы можем играться с промежуточными итогами для всей таблицы, а не для конкретного поля. Порой, это неудобно. Как в примере ниже:

Я бы хотел оставить промежуточный итог по сотруднику, а вот по месяцу убрать.
И сразу второй момент. Когда меняем макет сводной на табличный или форму структуры, то возникает вопрос с повторением подписей элементов, чтобы не было пустых ячеек. Если перейти на вкладку Конструктор - группа Макет - раскрыть Макет отчёта и выбрать Повторять все подписи элементов, то это сработает для всей таблицы. А я, например, хочу только по месяцам подписи.

Решение - великая и могучая правая кнопка мыши. Для того, чтобы добавить/убрать промежуточные итоги у определённого поля сводной таблицы, щёлкаем правой кнопкой мыши по любой ячейке этого поля и в контекстном меню находим Промежуточный итог...
Что касается подписей элементов, то щёлкаем правой кнопкой мыши по тому полю, где хотим/не хотим повторять подписи - далее Параметры поля... - на вкладке Разметка и печать находим галочку Повторять все подписи элементов.

7 - Объединение ячеек и выравнивание подписи по центру.

А если вам вообще не нужны все эти повторения, вы хотите объединённые ячейки с надписью по центру без всех этих плюсов/минусов, то и тут сводная сдюжит.

Решение - для объединения ячеек и выравнивания подписи по центру щёлкаем по любой ячейке сводной таблицы правой кнопкой мыши (данная настройка будет работать для всех полей сводной, для какого-то конкретного поля настроить возможности нет) - в контекстном меню находим Параметры сводной таблицы - вкладка Макет и формат - находим галочку Объединить и выровнять по центру ячейки с подписями.
А чтобы убрать плюсы/минусы, нужно выбрать любую ячейку сводной - далее вкладка Анализ сводной таблицы (в предыдущих версиях просто Анализ) - группа Показать - иконка Кнопки.

Заключение. На этом, пожалуй, всё. Вообще, всяких разных интересных штук при работе со сводной таблицей огромное множество. Про все и не напишешь. Делитесь в комментариях своими интересными приёмами. Думаю, многим будет интересно и полезно узнать что-то новое (мне так точно). Спасибо всем огромное, кто потратил своё драгоценное время и внимание на прочтение данного поста. Надеюсь, что-то из описанного было для вас полезным и вы сможете использовать это при работе со сводными таблицами.

Друзья, всем привет.

Сегодня хотел бы поговорить с вами о том, чего в Excel делать не нужно, чтобы в будущем избавить себя от головной боли и перманентных хлопков в нижней части тела. Планировал сначала про 10 вещей написать, но при подготовке материала понял, что в рамках одной статьи слишком уж много получится. Поэтому решил разбить на 2 части.

Ссылка на файл - https://disk.yandex.ru/i/tbnowzPQyiDpnw

Важное примечание!

Сразу оговорюсь, что речь здесь скорее пойдёт не про то, как исправлять уже кривые данные, а про то, как правильно первоначально их оформлять. Так что статья больше ориентирована не на тех, кто работает с готовыми данными, а собирает все свои таблицы "с нуля".

Что же, начнём.

1 - Объединение ячеек.

Один из самых страшных грехов при создании таблиц. Объединение ячеек влечёт за собой трудности с сортировкой, фильтрацией, созданием сводных, форматированием, протягиванием формул и т.д. Вот пример:

Ячейки в заголовках, где написаны месяцы, объединены. Если мне нужно будет просуммировать продажи за январь-февраль, то при переходе с 9 на 10 строку диапазон будет расширен до неприличного и ненужного мне. Придётся либо вручную его менять, либо через точку с запятой указывать несколько отдельных диапазонов.

Как сделать лучше.

Если уж так хочется, чтобы название месяца было по центру, то поступаем следующим образом.

Ничего не объединяя, пишем название месяца в первую ячейку (В2, если речь про ЯНВАРЬ), далее выделяем ячейки, в которых январь должен быть виден (В2:F2) - правая кнопка мыши - Формат ячеек (или CTRL + 1 с клавиатуры):

Далее вкладка Выравнивание - по горизонтали - по центру выделения:

К остальным заголовкам можно уже применить "Формат по образцу" (кстати, для многократного применения формата нужно нажать на иконку два раза левой кнопкой мыши), чтобы не проделывать все действия заново. Визуально получим то же самое, но без богомерзкого объединения ячеек.

2 - "Правильная" таблица

Этот пункт, безусловно, спорный, но я его всё же включил. И, в первую очередь, совет касается тех таблиц, но основе которых вы в дальнейшем планируете строить сводные таблицы. Сделали мы вот такую таблицу по месяцам за несколько лет:

Красиво, хорошо, душа радуется. Но грусть нас постигнет, если мы захотим потом построить сводную таблицу на основе этих данных. Нет, я не говорю, что это невозможно. Построим. И даже худо-бедно сможем годы просуммировать или месяцы. Но если бы таблица содержала все 12 месяцев или период не в 3 года, а лет в 5-6 хотя бы, то всё было бы куда хуже.

Как сделать лучше.

Всё просто - не делайте трёхмерные таблицы. Плоская таблица - вот то, что нам нужно. То есть выглядеть та же самая таблица должна вот так:

Теперь и сводную строить сплошное удовольствие, и сортировать/фильтровать данные куда проще. Про то, как трёхмерные таблицы переделывать в плоские не вручную, здесь речь не пойдёт.

3 - "Правильные" даты

Сразу заявлю, что я понимаю боль тех, кто сталкивается с уже волшебно введёнными датами. Но речь про то, как делать это правильно изначально. С датами в Excel вообще всё весело. Я хочу рассказать про самый, с моей точки зрения, чудесный способ ввод дат. Делаем мы таблицу, дошло дело до дат. И мы, как ответственные, трудолюбивые работники выбираем ячейку, и, как мама учила, начинаем вводить "29 марта 2023 г.". Целый день так работали в поте лица, устали. Довольные проделанной работой и гордые за себя. Но если вдруг нас просят отфильтровать данные только по одному конкретному месяцу или посчитать разницу между двумя датами, то мы впадаем в депрессию, потому что "тупой иксель" не фильтрует, не считает:

Ну не умеет он так даты воспринимать. Все мы неидеальны.

Как сделать лучше.

Вводить даты, используя православные разделители, которые Excel понимает. Их три:

1. / - слэш

2. . - точка

3. "-" - дефис/тире/минус (кому как удобнее)

А потом уже через формат ячеек установить то, что тебе нужно.

Про то, как исправлять уже сотворённое, думаю, отдельную статью напишу. Хотя в комментариях к предыдущим статьям разные способы мелькали. Соберу их в одном месте.

4 - Цветовое оформление таблицы.

Есть у нас унылые данные, никак не оформленные, а душа требует праздника и цвета. Начинаем аккуратно выделять столбцы и строки, усердно закрашиваем. Кто-то так вообще не церемонится, и выделяет сразу весь столбец и уверенно нажимает на ведро с краской:

А что? А вдруг новые данные появятся, я вот сразу этот момент продумал. Я - молодец. Потомки спасибо скажут. Нет, не скажут. Во-первых, это некрасиво. Во-вторых, это лишняя нагрузка на сам Excel. В-третьих, есть способ куда удобнее и практичнее.

Как сделать лучше.

Форматировать данные как таблицу.

Встаём в любую ячейку таблицы, Главная - Стили - Форматировать как таблицу. Выбираете цвет своего настроения (если ничего не хотите, то потом можно поменять или выбрать Создать свой стиль таблицы), проверяем диапазон и галочку "Таблица с заголовками" - Ок. Всего 4 (!!!) нажатия на кнопку мыши и готово. В этих наших интернетах это называется "умная" таблица. Инструмент обсуждаемый. Кто-то их боготворит, кто-то проклинает. Про всё здесь не расскажешь (наверное, отдельную статью писать придётся). Затрону только пару вещей. "Умная" таблица резиновая. То есть все столбцы и строки, которые мы будем добавлять вручную или копированием и вставкой, сразу будут отформатированы как и всё остальное в столбцах (включая формулы и форматы). Выделить столбец можно либо встав в любую ячейку столбца и нажав CTRL + пробел, либо наводим курсор на верхнюю часть ячейки с заголовком, ловим момент, когда он превратится в чёрную (чёрные стрелки важны) стрелку вниз, и нажимаем левую кнопку мыши.

5 - Ручное закрашивание данных

Нужно нам красивый табель сделать. Есть сотрудники, есть дни недели. Рабочие дни усердно и кропотливо закрашиваем через ведро с краской. Получается вот так:

Вроде бы, и хорошо. А как теперь посчитать, сколько человеки наши в неделю работали? Встроенные функции по цветам считать не умеют. Можно, конечно, пользовательскую функцию написать на VBA, но...

Как сделать лучше.

Условное форматирование плюс немного магии пользовательских форматов. В ячейки, где человек работал, ставим "1". Выделяем весь диапазон, где хотим что-то закрашивать. Главная - Стили - Условное форматирование - Правила выделения ячеек - Равно. Равно 1, в окошке справа выбираем пользовательский формат. На вкладке "Заливка" выбираем нужные нам цвета. А вот самое интересное происходит на вкладке "Число". Выбираем "(все форматы)", в поле "Тип" прописываем следующий формат ";;;" (без кавычек три точки с запятыми). Если кратко, то каждой точкой с запятой мы задаём формат для положительных, отрицательных, нулевых и текстовых значений. А так как ничего конкретного не прописали, Excel никак данные отображать и не будет. То есть физически в ячейке будет "1", но визуально ячейка будет пустой. Мало того, что мы теперь можем подсчитать количество рабочих дней с помощью СУММ или СЧЁТ, так ещё и при внесении изменений всё у нас будет меняться. Кстати, если делать это с "умной" таблицей, то при добавлении новых сотрудников созданное нами правило будет работать и на них.

Заключение

На этом, пожалуй, первую часть закончу. Как всегда, огромное спасибо всем, кто осилил данный материал, потратив своё драгоценное время и внимание. Надеюсь, было полезно. Делитесь своими соображениями по поводу того, чего делать НЕ НУЖНО, работая с данными в Excel. Совместными усилиями будем искоренять нечестивые данные.

Друзья, всем привет. В прошлой статье я рассказывал про мгновенное заполнение в Excel. Вещь интересная, но, как некоторые пользователи отмечали в комментариях (и вполне обоснованно), мгновенное заполнение не всегда подходит для решения тех или иных задач. В первую очередь, это касается данных, которые в таблице часто меняются. Мгновенное заполнение не реагирует на изменение данных. В этом случае лучше использовать функции. Да, это дольше, да, это будет выглядеть страшно (далее поймёте, почему), но зато один раз сделал и потом наслаждаешься плодами своих трудов. В качестве примера я решил взять тестовое задание, которое давно проходил при приёме на работу в одну довольно известную компанию.

Ссылка на файл - https://disk.yandex.ru/i/Ahq-9fH2XmS_3Q

Важное уточнение! В Excel можно решать очень сложные задачи. Но должна быть хоть какая-то логика. Чем сложнее логика, тем сложнее будут сочетания функций, набор инструментов и т.д. Потратишь много времени, но решишь. А вот если логики нет, то не ждите чуда. Чудите сами, как говорится.

Что же, давайте приступим.

Задача

Есть столбец с текстом:

Нам необходимо в отдельные столбцы разнести код и наименование.

Решение

Первое, на что обращаем внимание, есть ли здесь закономерность? С кодом всё понятно. Нужно извлечь текст до первого пробела. Отлично. Находим порядковый номер первого пробела с помощью функции НАЙТИ (FIND):

Далее всё просто. С помощью функции ЛЕВСИМВ (LEFT) извлекаем всё, что до пробела, отнимая от порядкового номера пробела единицу:

Я показал пошаговое решение. Но это можно всё сделать и в одной формуле. Вместо D2 вставляем НАЙТИ (FIND):

Перейдём к более интересному - наименование. И снова начинаем с логики. Нужно извлечь текст между первым и последним пробелом. В Excel нет функции, которая по щучьему велению, по нашему хотению извлекает текст между первым и последним символом (например, пробелами). Придётся хитрить. Сначала пошагово, потом впихнём всё в одну ячейку (и оторвёмся по полной).

Надо подсчитать, а сколько в принципе у нас пробелов в тексте. Сделать это можно вот как:

ДЛСТР (LEN)(А2) (как вам название?) - подсчитывает общее количество символов в ячейке. ДЛСТР(ПОДСТАВИТЬ(A2;" ";"")) - подсчитывает количество символов без пробелов. От них мы избавились благодаря функции ПОДСТАВИТЬ (SUBSTITUTE), которая заменила пробелы на пусто.

Теперь очень мудрёный шаг. Нам надо заменить последний пробел на какой-нибудь другой произвольный символ:

ПОДСТАВИТЬ (SUBSTITUTE)(A2;" ";"*";E2) как раз это и делает. Даём ей исходный текст, что нужно найти, на что заменить, и какой символ по порядку (именно для этого нам нужно было узнать, сколько всего пробелов).

Зачем нам это? Немного терпения, друзья, сейчас всё будет.

Логика решения меняется. Теперь нам надо извлечь текст между первым пробелом и звёздочкой. А вот это реализовать уже можно. Находим порядковый номер звёздочки:

Финальный шаг:

ПСТР (MID) - как много в этом слове... А функция, вообще-то, полезная. Указываем ей ячейку, порядковый номер, с которого хотим начать извлекать символы, и само количество символов.

А2 - понятно, ячейка с текстом.

D2+1 - начиная с какого символа будем извлекать (первый пробел + 1)

G2-D2-1 - количество извлекаемых символов. Порядковый номер звёздочки - порядковый номер первого пробела - 1 (чтобы исключить лишний символ в конце).

А теперь комбо! Всё в одной ячейке:

Говорил же, что будет страшно, а вы не верили.

Заключение

Вот и решение задачи. Вообще, моё мнение, такие штуки в любом случае нужно сначала решать пошагово, а потом только впихивать всё в одну ячейку. Но тут уж каждый сам для себя решает.

В файле, который идёт к этой статье, есть ещё один готовый пример. Это тоже вполне себе реальная задача, которую слушатели предложили решить во время корпоративного обучения. Исходный текст нужно было переделать в то, что указано в "Должно быть":

Здесь уже расписывать не буду, а то ещё одна "простыня" получится. Кому интересно, посмотрите. Там, правда, уже не так всё подробно по шагам расписано. Но я в вас верю!

Можно и ещё более сложные конструкции использовать. В комментариях к прошлой статье были варианты с обработкой пустых ячеек. Про всё не расскажешь. Если честно, я перед собой такую цель и не ставлю. Главное, что я хотел вам показать - это то, что прежде, чем кидаться искать функции, посмотрите, а есть ли вообще логика в том, что вы хотите? Если есть, тогда и решение найдётся.

Как всегда, огромное всем спасибо, кто потратил своё драгоценное время и внимание на чтение данного материала. Надеюсь, что-то вам обязательно пригодится. Пишите комментарии, делитесь своими мыслями, решениями и вопросами. Да пребудет с нами сила ИКСэль.

П.С. недавно проводил мастер-класс по разным штукам в Excel, про которые здесь почти не писал. Например, рассказал про то, что такое расширенный фильтр.

Ссылка - (59) Полезные приёмы при работе в Excel. Часть 2. - YouTube

Друзья, всем привет. Сегодня хочу рассказать вам про мгновенное заполнение в Excel.

Ссылка на файл, чтобы можно было потренироваться - https://disk.yandex.ru/i/HyW0N215F6CuUg

Возможно, многие с ним знакомы заочно. Наверняка же замечали, что когда вручную заполняешь какие-то значения в ячейках, то с переходом к следующей ячейке при вводе символов Excel порой выдаёт вот такой список:

Так вот это и есть мгновенное заполнение во всей своей красе. Да, иногда это раздражает, потому что тебе это не нужно. Но в большинстве случае польза мгновенного заполнения огромна.

Извлечение данных

Предположим, у нас есть вот такой столбец с текстом:

Нам нужно извлечь отдельно номер договора и дату. Это можно сделать с помощью инструмента "Текст по столбцам". Правда, потом придётся от символа "№" ещё избавляться. А вот мгновенное заполнение справится с этим намного быстрее. Просто вводим справа от текста в первую ячейку номер договора (1), нажимаем Enter. Далее возможны два варианта.

Вариант 1. Вручную вводим в ячейку первую цифру второго договора (2). Excel предлагает свои варианты, жмём Enter - PROFIT!

Вариант 2. После того, как перешли ко второй ячейке, сразу нажимаем сочетание Ctrl + E (Е английская, конечно). Именно это сочетание отвечает за запуск мгновенного заполнения. Аналогично с датами. Вводим в ячейку С2 дату первого договора - Enter - Ctrl + E - наслаждаемся результатом.

ОЧЕНЬ ВАЖНАЯ ЧАСТЬ СТАТЬИ.

Так как же это работает? Всё довольно просто. В первой ячейке мы задаём образец, чего хотим получить, далее Excel распознаёт нашу логику и заполняет остальные ячейки по образу и подобию.

Ух ты! И так будет работать всегда?! Строго говоря - нет. Иногда, Excel не может с одной ячейки распознать логику. В этом случае нужно вручную заполнить не одну, а две, три, четыре (если случай совсем запущенный) ячейки. И только после этого нажимать Ctrl + E. Чем больше ячеек заполняешь, тем выше вероятность того, что твоя логика будет верно распознана могучим интеллектом Excel. Порой мгновенное заполнение не справляется с поставленной задачей:

Даты в первом столбце указаны в формате ГГГГ-ММ-ДД. При попытке привести их в формат ДД-ММ-ГГГГ получается вот такая "красота". Поэтому не поленитесь после того, как все ячейки будут заполнены, пробежаться по ним, а тот ли в них результат, который ты ожидал увидеть.

Образцы вводите в соседнем столбце от источника (можно справа или слева). Не "убегайте" далеко от данных, результат может быть непредсказуемым или вообще ничего не будет.

Ещё одно важное дополнение: мгновенное заполнение работает в версиях Excel 2013 и выше.

Теперь, когда с пояснениями закончено, давайте посмотрим, на что ещё способен этот удивительный инструмент.

Извлечение только чисел из столбца

Если нам из "красивого" столбца, в котором есть значения вроде "123руб", "55 рублей" и так далее, нужно извлечь только цифры, то вы уже знаете, что нам поможет:

В данном конкретном случае я прописал вручную две первых ячейки, иначе Excel не понимал, что нужны только числа.

Работа с текстом

В столбце указаны Имя и Фамилия. Нам нужно получить результат в виде "Имя Ф." В первой ячейке вводим образец - Enter - Ctrl + E:

Кстати, если попробовать получить Фамилия И., то будьте внимательны. Если прописать два примера, потом начать вводить третий, то появляется довольно забавный список:

Но если не начинать вводить в третью ячейку текст, а сразу нажать на Ctrl + E, то всё будет нормально. Раз на раз не приходится. Временами мгновенное заполнение ведёт себя очень странно.

Извлечение части сплошного текста

Необходимо разбить слипшийся текст на части. Вводим в первых двух ячейках образец - Ctrl + E:

С номером поступаем аналогично.

Сбор текста

В отдельных столбцах есть различная информация, которую необходимо собрать в одно предложение. Обратите внимание, что порядок столбцов для мгновенного заполнения роли не играет. Прописываем предложение в первой ячейке - Enter - Ctrl + E:

Заключение

На этом статью я хотел бы завершить. Уверен, я перечислил далеко не все чудесные возможности мгновенного заполнения. Буду вам благодарен, если в комментариях поделитесь своими способами применения этой чудесной штуки.

В качестве небольшой рекламы позвольте оставить здесь ссылку на мастер-класс, который я буду проводить 9 марта. Кто хочет узнать ещё несколько полезных приёмов при работе в Excel (там почти не будет того, о чём я писал здесь), а ещё хочет услышать чуть больше про то, где я работаю, записывайтесь - Полезные приемы при работе в Excel. Часть 2 (specialist.ru)

На этом всё. Как обычно, спасибо огромное всем, кто потратил своё драгоценное время и осилил данное полотно. Надеюсь, было полезно. Видео по данной статье обязательно появится на моём канале - (36) Андрей Митрохин - YouTube